Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organising data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of “electronic spreadsheets.” Better known simply as “spreadsheets,” these software programs provide a computerised replacement for the traditional financial modelling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this “pad” exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, and a letter to each column. To reference a location at column A and row 1 (i.e., the upper-left hand corner), for example, the user types in “A1”. In this manner, the spreadsheet defines an addressable storage location or “cell” at each intersection of a row with a column.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or “formulas” specifying calculations to be performed on the numbers stored in spreadsheet cells. In this fashion, cell references can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-OsbomelMcGraw-MII, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que corp., 1985. The disclosures of each of the foregoing are hereby incorporated by reference.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform “what-if” scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas and/or built-in functions for instance, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve “what-if” problems, that is, changing an input and seeing what happens to an output.
As explained above, one essential value of an electronic spreadsheet is to organise data into columns and rows, and to automate tedious calculations. A typical, common, and valuable example of data organised into columns and rows is a table or a range of cells. Ranges of cells therefore are quite useful objects. A range of cells or a table can be defined and processed as a whole entity. For example it can be copied from one place to another within a single spreadsheet file or from one spreadsheet file to another spreadsheet file.
It is a common operation in electronic spreadsheet environments, to create and manipulate quite large tables comprising a large numbers of rows and columns (or even sheets when such tables are organised according to a three dimensional structure). With conventional spreadsheet tools, the user interface displays a certain number of rows and columns. However large tables can easily occupy a space spanning beyond the limits of the user interface. As a result large tables cannot be easily entirely visualised. This can be detrimental from a user friendliness standpoint and for a matter of efficiency:
When different important pieces of information spread within a given large table cannot be visualised at the same time, the user may waste a lot of time switching between different views. The user is often obliged to take notes before swapping between these views.
When key information is spread over a large table comprising a lot of intermediate information, the user may waste a lot of time scanning the data to identify the relevant information.
When the user needs to summarize or synthesize key information in a smaller table, he may waste a lot of time performing manual copy-paste operation to obtain the expected synthesis.
Some conventional tools are available in electronic spreadsheet environments, to address this need, but unfortunately these tools present important limitations as detailed hereunder.
A first tool that can be used for visualizing a large table, is the zoom function. The zoom function is similar to an optical zoom in the sense that it applies a user-controlled scaling factor to the display device. By adjusting the zooming ratio, more elements of information (i.e. more rows or columns) can be displayed. Unfortunately, this is achieved by reducing the size (width and height) of the displayed elements. This operation can be done until the elements become unreadable on the display device. It is like attempting to display the whole map of the city of New York while wishing to read the name of each street or avenue. Moreover, within a huge amount of data, the user must sort which data deserve the attention and must be discarded to understand/address a given topic.
A second tool that can be used for visualizing a whole table is the Outline function. The Outline function is based on dominant-subordinate relationships (or summary-detail relationships) between either rows or columns. The spreadsheet user first specifies relationships between either rows and columns, to associate a set of contiguous “subordinate” (or “detail”) rows (respectively columns) with a contiguous “dominant” (or “summary”) row (respectively column). Although this kind of relationship may be easily defined in some conventional situations (for instance twelve monthly records associated with a yearly record), there are many cases where relationships can not be established between contiguous rows. The condition imposing that the “dominant” or (summary) row or column is contiguous to its related “subordinate” (or detail) rows or columns constitutes an important constraint with severe consequences on the structure of the table. Once the above relationships are established between different subsets of rows and/or columns, the electronic spreadsheet user can:
either collapse a subset of “subordinate” (or detail) rows or columns, so that only the associated “dominant” (or summary) row or column is displayed, or
expand a “dominant” (or summary) row or column, so that the associated hidden set of “subordinate” (or detail) rows or columns is displayed again.
With the Outline function, the spreadsheet user can decide for each subset of rows or columns participating to a given relationship, to hide or display the “subordinate” rows or columns. When multiple relationships of that kind are defined, the hide/display operation can be done for each defined relationship, or for all subsets at a given level. However, this operation is time consuming for large tables. The Outline function allows to nest different levels of relationship. However, the definition and manipulation of different levels of relationship is relatively complex. Furthermore, once an Outline structure has been defined, it is quite complex to change the Outline level of a given row because this operation requires the suppression and the recreation of the Outline structure. Another strong limitation of the Outline function comes from the fact that it does not relate to a given table (or range of cells), but to a worksheet within the spreadsheet. The Outline function does not address the case where multiple tables, not necessarily of the same nature, belong to the same worksheet.
The Outline approach is described in U.S. Pat. No. 5,255,356 filed by Microsoft Corporation.
The present invention offers the possibility to copy and paste the visible part of a range of cells. In conventional spreadsheet environments, this operation cannot be done because any hidden row or column within a given range of cells is still treated like a visible element by the copy-and-paste method. With the “Copy visible part” function, only a Copy/Paste by Value is performed. In the present invention, attention is given to the way the paste operation is performed to keep as much information as possible, including formula defined relationships, while avoiding building a range of cells with unresolved references.
In a particular embodiment of the invention, a specific method is used for easily controlling the display and hiding by the spreadsheet user, of elements (rows/columns/sheets) within a range of cells. This method is based on the following principles:
Each object is associated with a given range of cells, so that two or more different ranges of cells (or tables), even if they belong to the same worksheet, can be managed independently.
Under the control of the spreadsheet user, for each managed range of cells, a set of focus levels arranged as a list of ordered levels is created, so that a “display hierarchy” can be defined and customized by the spreadsheet user.
Each element (row/column/sheet) of a managed range of cells is associated with a defined focus level. This association can be explicit (“position based levels” in the description of the embodiment) or can be implicit, depending either on cell attributes (“attribute based levels” in the description of the embodiment), or on cell content (“content based levels” in the description of the embodiment).
In the first case, each element of a managed range of cells, points to a given focus level.
In the second case, a focus level is associated either with a set of display attributes (e.g. font type, or font color, or background color, or border line, etc.), or with a cell content criteria (e.g. a numerical value being larger than a predefined threshold). Then each element (row/column/sheet) of a managed range of cells is associated either with the focus level matching the display attribute of the heading cell of the element or with the focus level matching the content criteria of the cell within the element with a predefined relative offset.
A current display focus level is defined.
Upon a user action, all the elements (rows/columns/sheets) having a focus level lower than the current display focus level are hidden.
All the above functions are accessible through a set of dedicated user interfaces that can be easily launched through conventional means like pushbuttons, keyboard short keys, menu or sub-menu entries.